#!/bin/bash
source /etc/profile
day=$(date +%Y-%m-%d)
yt=`date -d "1 days ago "  "+%Y-%m-%d"`
daythirty=$(date --date='30 days ago' +%Y-%m-%d)
daysixty=$(date --date='60 days ago' +%Y-%m-%d)
month=$(date +%Y-%m)
echo $day

cd /home/marvin/hive-1.1.0-cdh5.6.0

echo 'before.hql'
bin/hive -f /home/marvin/daily_job/hive/before.hql
echo 'pos.hql'
bin/hive -f /home/marvin/daily_job/hive/pos.hql -d dt=$day
sleep 1m
echo 'posvip.hql'
bin/hive -f /home/marvin/daily_job/hive/posvip.hql
sleep 1m
echo 'beijingvip.hql'
bin/hive -f /home/marvin/daily_job/hive/beijingvip.hql  -d dt=$day

part1(){
echo 'chengdevip.hql'
   bin/hive -f /home/marvin/daily_job/hive/chengdevip.hql  -d dt=$day
   sleep 1m
   bin/hive -f /home/marvin/daily_job/hive/b.hql
   sleep 1m
   bin/hive -f /home/marvin/daily_job/hive/coupon.hql
   sleep 1m
   bin/hive -f /home/marvin/daily_job/hive/hoseefficiency.hql
   bin/hive -f /home/marvin/daily_job/hive/rule.hql
}
part2(){
   echo 'serve.hql'
   sleep 1m
   bin/hive -f /home/marvin/daily_job/hive/serve.hql
   sleep 1m
   bin/hive -f /home/marvin/daily_job/hive/RFM.hql
   sleep 1m
   bin/hive -f /home/marvin/daily_job/hive/tag.hql
   bin/hive -f /home/marvin/daily_job/hive/tag_cd.hql
}

 part1  >/home/marvin/daily_job/logs/`date +%Y-%m-%d`/hqlpart1.log 2>&1  &
 part2  >/home/marvin/daily_job/logs/`date +%Y-%m-%d`/hqlpart2.log 2>&1  &
wait


#bin/hive -f /home/marvin/daily_job/hive/coupon_sum.sql  -d dt=$yt
bin/hive<<EOF

insert into table mid_vipThirtyDaysRate
SELECT '$daythirty',COUNT(*)
FROM
(select CARDUSER_ID FROM TBL_CARDUSER WHERE RECORDTIME='$daythirty')AS A
JOIN
(select CARDUSERID 
FROM TBL_ORDERDETAILS WHERE SENDTIME >'$daythirty' AND SENDTIME < DATE_ADD('$daythirty',30)
GROUP BY CARDUSERID)AS B
ON A.CARDUSER_ID=B.CARDUSERID;

insert overwrite table res_vipThirtyDaysRate
select A.DATE,A.COUNT,B.conversionNum
from res_addvip as A
join mid_vipThirtyDaysRate as B
ON A.DATE=B.DAYS;

insert into table mid_vipSixtyDaysRate
SELECT '$daysixty',COUNT(*)
FROM
(select CARDUSER_ID FROM TBL_CARDUSER WHERE RECORDTIME='$daysixty')AS A
JOIN
(select CARDUSERID 
FROM TBL_ORDERDETAILS WHERE SENDTIME >'$daysixty' AND SENDTIME < DATE_ADD('$daysixty',60)
GROUP BY CARDUSERID)AS B
ON A.CARDUSER_ID=B.CARDUSERID;

insert overwrite table res_vipSixtyDaysRate
select A.DATE,A.COUNT,B.conversionNum
from res_addvip as A
join mid_vipSixtyDaysRate as B
ON A.DATE=B.DAYS;

insert into table mid_vipDrain
select '$day',count(*)
from
(select CARDUSERID
FROM TBL_ORDERDETAILS where SENDTIME >=date_sub('$day',90) AND SENDTIME <='$day' GROUP BY CARDUSERID)a;

insert overwrite table res_vipDrain
select A.date,A.sum-B.livenum,A.sum
from res_addvip as A join mid_vipDrain as B
on A.date=B.day;





insert into table mid_vipThirtyDaysRate_cd
SELECT '$daythirty',COUNT(*)
FROM
(select CARDUSER_ID FROM TBL_CARDUSER_CD WHERE RECORDTIME='$daythirty')AS A
JOIN
(select CARDUSERID 
FROM TBL_ORDERDETAILS_CD WHERE SENDTIME >'$daythirty' AND SENDTIME < DATE_ADD('$daythirty',30)
GROUP BY CARDUSERID)AS B
ON A.CARDUSER_ID=B.CARDUSERID;

insert overwrite table res_vipThirtyDaysRate_cd
select A.DATE,A.COUNT,B.conversionNum
from res_addvip_cd as A
join mid_vipThirtyDaysRate_cd as B
ON A.DATE=B.DAYS;

insert into table mid_vipSixtyDaysRate_cd
SELECT '$daysixty',COUNT(*)
FROM
(select CARDUSER_ID FROM TBL_CARDUSER_CD WHERE RECORDTIME='$daysixty')AS A
JOIN
(select CARDUSERID 
FROM TBL_ORDERDETAILS_CD WHERE SENDTIME >'$daysixty' AND SENDTIME < DATE_ADD('$daysixty',60)
GROUP BY CARDUSERID)AS B
ON A.CARDUSER_ID=B.CARDUSERID;

insert overwrite table res_vipSixtyDaysRate_cd
select A.DATE,A.COUNT,B.conversionNum
from res_addvip_cd as A
join mid_vipSixtyDaysRate_cd as B
ON A.DATE=B.DAYS;

insert into table mid_vipDrain_cd
select '$day',count(*)
from
(select CARDUSERID
FROM TBL_ORDERDETAILS_CD where SENDTIME >=date_sub('$day',90) AND SENDTIME <='$day' GROUP BY CARDUSERID)a;

insert overwrite table res_vipDrain_cd
select A.date,A.sum-B.livenum,A.sum
from res_addvip_cd as A 
join mid_vipDrain_cd as B
on A.date=B.day;

EOF



